package site.ruyi.devclient.util;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {

	private DataSource dataSource=null;

	private DBUtil(){}

	private DBUtil(DataSource dataSource){
		this.dataSource=dataSource;
	}

	public static DBUtil getInstance(DataSource dataSource){
		return new DBUtil(dataSource);
	}

	public static void close(Connection conn){
		try {
			if(conn!=null){
				conn.close();
				conn=null;
			}
		} catch (SQLException e) {
			throw new UnsupportedOperationException(e);
		}
	}

	public static void close(Statement statement){
		try {
			if(statement!=null){
				statement.close();
				statement=null;
			}
		} catch (SQLException e) {
			throw new UnsupportedOperationException(e);
		}
	}

	public static void close(ResultSet resultSet){
		try {
			if(resultSet!=null){
				resultSet.close();
				resultSet=null;
			}
		} catch (SQLException e) {
			throw new UnsupportedOperationException(e);
		}
	}

	public static void close(Connection conn,Statement statement){
		close(conn,statement,null);
	}

	public static void close(Statement statement,ResultSet resultSet){
		close(null,statement,resultSet);
	}

	public static void close(Connection conn,Statement statement,ResultSet resultSet){
		close(resultSet);close(statement);close(conn);
	}

	public Connection getConnection(){
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new UnsupportedOperationException(e);
		}
	}

	public void exec(ConnectionExecTemplate template){
		Connection conn=getConnection();
		try {
			template.exec(conn);
		}finally{
			close(conn);
		}
	}

	public void exec(final String sql){
		if(sql==null||"".equals(sql)) return;
		exec((Connection conn)->{
			Statement statement=null;
			try {
				statement=conn.createStatement();
				statement.execute(sql);
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}finally{
				close(statement);
			}
		});
	}

	public void exec(final String sql,StatementExecTemplate template){
		exec((Connection conn)->{
			PreparedStatement statement=null;
			try {
				statement = conn.prepareStatement(sql);
				template.initParams(new SqlBuider(statement));
				statement.execute();
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}finally{
				close(statement);
			}
		});
	}

	public void exec(final String sql,final Object... params){
		exec(sql,new StatementExecTemplate(){
			@Override
			public void initParams(SqlBuider statement) {
				if(params==null||params.length==0) return;
				for(int i=0,size=params.length;i<size;i++){
					if(params[i]==null){
						statement.setNull();
					}else if(params[i] instanceof String){
						statement.setString((String)params[i]);
					}else{
						statement.setObject(params[i]);
					}
				}
			}
		});
	}

	public int save(ConnectionUpdateTemplate template){return update(template);}
	public int delete(ConnectionUpdateTemplate template){return update(template);}

	public int update(ConnectionUpdateTemplate template){
		Connection conn=getConnection();
		try {
			return template.update(conn);
		}finally{
			close(conn);
		}
	}

	public int save(final String sql){return update(sql);}
	public int delete(final String sql){return update(sql);}

	public int update(final String sql){
		if(sql==null||"".equals(sql)) return 0;
		return update((Connection conn)->{
			Statement statement=null;
			try {
				statement=conn.createStatement();
				return statement.executeUpdate(sql);
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}finally{
				close(statement);
			}
		});
	}

	public int save(final String sql,StatementUpdateTemplate template){return update(sql,template);}
	public int delete(final String sql,StatementUpdateTemplate template){return update(sql,template);}

	public int update(final String sql,StatementUpdateTemplate template){
		return update((Connection conn)->{
			PreparedStatement statement=null;
			try {
				statement = conn.prepareStatement(sql);
				template.initParams(new SqlBuider(statement));
				return statement.executeUpdate();
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}finally{
				close(statement);
			}
		});
	}

	public int save(final String sql,Object... params){return update(sql,params);}
	public int delete(final String sql,Object... params){return update(sql,params);}

	public int update(final String sql,Object... params){
		return update(sql,new StatementUpdateTemplate(){
			@Override
			public void initParams(SqlBuider statement) {
				if(params==null||params.length==0) return;
				for(int i=0,size=params.length;i<size;i++){
					if(params[i]==null){
						statement.setNull();
					}else if(params[i] instanceof String){
						statement.setString((String)params[i]);
					}else{
						statement.setObject(params[i]);
					}
				}
			}
		});
	}

	public List<Map<String,Object>> query(final String sql, StatementQueryTemplate template){
		return query((Connection conn)->{
			PreparedStatement statement=null;
			try {
				statement = conn.prepareStatement(sql);
				template.initParams(new SqlBuider(statement));
				return fetchAll(statement.executeQuery());
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}finally{
				close(statement);
			}
		});
	}

	public List<Map<String,Object>> query(final String sql,Object... params){
		return query(sql,new StatementQueryTemplate(){
			@Override
			public void initParams(SqlBuider statement) {
				if(params==null||params.length==0) return;
				for(int i=0,size=params.length;i<size;i++){
					if(params[i]==null){
						statement.setNull();
					}else if(params[i] instanceof String){
						statement.setString((String)params[i]);
					}else{
						statement.setObject(params[i]);
					}
				}
			}
		});
	}

	public List<Map<String,Object>> query(ConnectionQueryTemplate template){
		Connection conn=getConnection();
		try {
			return template.query(conn);
		}finally{
			close(conn);
		}
	}

	public List<Map<String,Object>> query(final String sql){
		if(sql==null||"".equals(sql)) return null;
		return query((Connection conn)->{
			Statement statement=null;
			try {
				statement=conn.createStatement();
				return fetchAll(statement.executeQuery(sql));
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}finally{
				close(statement);
			}
		});
	}

	public Map<String,Object> find(final String sql,StatementQueryTemplate template){
		List<Map<String,Object>> result=query(sql,template);
		return (result!=null&&result.size()>0)?result.get(0):null;
	}

	public Map<String,Object> find(final String sql,Object... params){
		List<Map<String,Object>> result=query(sql,params);
		return (result!=null&&result.size()>0)?result.get(0):null;
	}

	public Map<String,Object> find(ConnectionQueryTemplate template){
		List<Map<String,Object>> result=query(template);
		return (result!=null&&result.size()>0)?result.get(0):null;
	}

	public Map<String,Object> find(final String sql){
		List<Map<String,Object>> result=query(sql);
		return (result!=null&&result.size()>0)?result.get(0):null;
	}

	public List<Map<String,Object>> fetchAll(ResultSet result) throws SQLException{
		List<Map<String,Object>> data=new ArrayList<>();
		if(result==null) return data;
		try{
			while(result.next()){
				Map<String,Object> rowData=new HashMap<>();
				for(int i=1,count=result.getMetaData().getColumnCount();i<=count;i++){
					Object value = result.getObject(i);
					if(value instanceof Clob && value!=null){
						rowData.put(result.getMetaData().getColumnLabel(i),clobToString((Clob)value));
					}else{
						rowData.put(result.getMetaData().getColumnLabel(i),result.getObject(i));
					}
				}
				data.add(rowData);
			}
		}finally{
			close(result);
		}
		return data;
	}

	private String clobToString(Clob clob){
		String reString = "";
		Reader is = null;
		BufferedReader br = null;
		try{
			is = clob.getCharacterStream();
			br = new BufferedReader(is);
			String s = br.readLine();
			StringBuffer sb = new StringBuffer();
			while (s != null) {
				sb.append(s);
				s = br.readLine();
			}
			reString = sb.toString();
		}catch (Exception e){
			throw new UnsupportedOperationException(e);
		}finally {
			if(br!=null){
				try {
					br.close();
				} catch (IOException e) {
					throw new UnsupportedOperationException(e);
				}
			}
			if(is!=null){
				try {
					is.close();
				} catch (IOException e) {
					throw new UnsupportedOperationException(e);
				}
			}
		}

		return reString;
	}

	@FunctionalInterface
	public static interface ConnectionExecTemplate{
		void exec(Connection conn);
	}

	@FunctionalInterface
	public static interface ConnectionUpdateTemplate{
		int update(Connection conn);
	}

	@FunctionalInterface
	public static interface ConnectionQueryTemplate{
		List<Map<String,Object>> query(Connection conn);
	}

	@FunctionalInterface
	public static interface StatementExecTemplate{
		void initParams(SqlBuider statement);
	}

	@FunctionalInterface
	public static interface StatementUpdateTemplate{
		void initParams(SqlBuider statement);
	}

	@FunctionalInterface
	public static interface StatementQueryTemplate{
		void initParams(SqlBuider statement);
	}

	private static class SqlBuider{

		private int index=1;
		private PreparedStatement statement=null;

		public SqlBuider(PreparedStatement statement){
			this.statement=statement;
		}

		public SqlBuider setNull(){
			if(statement==null) throw new NullPointerException("DB statement is null!!!");
			try {
				this.statement.setNull(this.index,Types.NULL);
				this.index++;
				return this;
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}
		}

		public SqlBuider setString(String val){
			if(statement==null) throw new NullPointerException("DB statement is null!!!");
			try {
				this.statement.setString(this.index,val);
				this.index++;
				return this;
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}
		}

		public SqlBuider setObject(Object val){
			if(statement==null) throw new NullPointerException("DB statement is null!!!");
			try {
				if(val==null){
					this.statement.setNull(this.index, Types.NULL);
				}else{
					this.statement.setObject(this.index,val);
				}
				this.index++;
				return this;
			} catch (SQLException e) {
				throw new UnsupportedOperationException(e);
			}
		}
	}
}